package org.tnc.budget.webapp.controller;

import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.tnc.budget.model.BudgetCenter;
import org.tnc.budget.model.EventTable;
import org.tnc.budget.service.BudgetManager;
import org.tnc.budget.service.EventManager;
import org.tnc.model.User;
import org.tnc.service.UserManager;

@Controller
public class AnalyseExcelController {

	private static final boolean VACATION = true;

	private static final boolean NOT_VACATION = false;

	private static final boolean BC_VALID = true;

	private UserManager userManager;

	private EventManager eventManager;

	private BudgetManager budgetManager;
	
	public static void main(String [] args) {
		EventTable et = new EventTable();
		et.setStartDate("2011-06-02");
		et.setStartTime("11:30:00");
		et.setEndDate("2011-06-02");
		et.setEndTime("12:00:00");
		System.out.println((et).getDuration());
	}
	
	private static void setBorder(CellStyle cellStyle, short borderStyle, short borderColor) {
		cellStyle.setBorderBottom(borderStyle);
		cellStyle.setBottomBorderColor(borderColor);
		
		cellStyle.setBorderLeft(borderStyle);
		cellStyle.setLeftBorderColor(borderColor);
		
		cellStyle.setBorderRight(borderStyle);
		cellStyle.setRightBorderColor(borderColor);
		
		cellStyle.setBorderTop(borderStyle);
		cellStyle.setTopBorderColor(borderColor);
	}

	private static void createTitle(int columnIndex, String title, CellStyle cellStyle, XSSFSheet sheet, XSSFRow ...rows) {
		boolean firstrow = true;
		int startRowIndex = rows[0].getRowNum();
		int endRowIndex = rows[rows.length - 1].getRowNum();
		for (XSSFRow xssfRow : rows) {
			XSSFCell c = xssfRow.createCell(columnIndex);
			c.setCellStyle(cellStyle);
			if (firstrow) {
				firstrow = false;
				c.setCellValue(title);
			}
		}
		sheet.addMergedRegion(new CellRangeAddress(startRowIndex, endRowIndex, columnIndex, columnIndex));
		sheet.setColumnWidth(columnIndex, 10 * 256);
	}
	
	@RequestMapping("/analyseForm")
	public String analyseForm() {
		return "/event/analyse";
	}
	
	/**
	 * @param request
	 * @param response
	 */
	@RequestMapping("/downloadAnalyseExcel")
	public void download(HttpServletRequest request,
			HttpServletResponse response) {

		String month = request.getParameter("month");

		String year = request.getParameter("year");

		if (month == null || year == null) {
			return;
		}

		try {
			
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment;filename=MonthlyAnalyse-" + year+ "-" + month + ".xlsx");
			
			/*
			 * 查询所有非假期budget center
			 */
			List<BudgetCenter> bclist = budgetManager.getAll(BC_VALID, NOT_VACATION);
			List<BudgetCenter> vacationList = budgetManager.getAll(BC_VALID, VACATION);
			
			List<User> users = userManager.getUsers();

			XSSFWorkbook wb = new XSSFWorkbook();
			
			CellStyle csNormal = wb.createCellStyle();
			setBorder(csNormal, CellStyle.BORDER_THIN, IndexedColors.BLACK.getIndex());
			XSSFFont font = wb.createFont();
			font.setBold(false);
			csNormal.setFont(font);
			
			CellStyle csBold = wb.createCellStyle();
			setBorder(csBold, CellStyle.BORDER_THIN, IndexedColors.BLACK.getIndex());
			font = wb.createFont();
			font.setBold(true);
			csBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			csBold.setAlignment(CellStyle.ALIGN_CENTER);
			csBold.setWrapText(true);
			csBold.setFont(font);
			
			CellStyle csAllocSal = wb.createCellStyle();
			csAllocSal.cloneStyleFrom(csNormal);
			csAllocSal.setDataFormat(wb.createDataFormat().getFormat("_(* #,##0.00_);_(* (#,##0.00);_(* \"-\"??_);_(@_)"));
			csAllocSal.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
			csAllocSal.setFillPattern(CellStyle.SOLID_FOREGROUND);
			
			CellStyle csPercent = wb.createCellStyle();
			csPercent.cloneStyleFrom(csNormal);
			csPercent.setDataFormat(wb.createDataFormat().getFormat("0%"));
			
			XSSFSheet sheet = wb.createSheet();
			XSSFRow r0 = sheet.createRow(0);
			XSSFRow r1 = sheet.createRow(1);
			r1.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints()));
			
			createTitle(0, "员工号", csBold, sheet, r0, r1);
			createTitle(1, "员工姓名", csBold, sheet, r0, r1);
			createTitle(2, "Total Hours", csBold, sheet, r0, r1);
			createTitle(3, "Monthly Rate", csBold, sheet, r0, r1);
			
			int i = 4;
			for (Iterator<BudgetCenter> iterator = bclist.iterator(); iterator.hasNext(); i += 3) {
				BudgetCenter bc = iterator.next();
				
				XSSFCell c0 = r0.createCell(i+0);
				XSSFCell c1 = r0.createCell(i+1);
				XSSFCell c2 = r0.createCell(i+2);
				
				c0.setCellStyle(csBold);
				c1.setCellStyle(csBold);
				c2.setCellStyle(csBold);
				c0.setCellValue(bc.getBcName() + ": " + bc.getBcId());
				sheet.addMergedRegion(new CellRangeAddress(0, 0, i, i+2));
				
				c0 = r1.createCell(i+0);
				c0.setCellStyle(csBold);
				c0.setCellValue("Hours");
				
				c1 = r1.createCell(i+1);
				c1.setCellStyle(csBold);
				c1.setCellValue("%");
				
				c2 = r1.createCell(i+2);
				c2.setCellStyle(csBold);
				c2.setCellValue("Allocate\nSalary");
			}
			
			for (Iterator<BudgetCenter> iterator = vacationList.iterator(); iterator.hasNext(); i += 1) {
				BudgetCenter bc = iterator.next();
				XSSFCell c0 = r1.createCell(i);
				c0.setCellStyle(csBold);
				c0.setCellValue(bc.getBcName());
			}
			
			int userRowIndex = 2;
			for (Iterator<User> iterator = users.iterator(); iterator.hasNext(); userRowIndex++) {
				XSSFRow r = sheet.createRow(userRowIndex);
				XSSFCell thcell = r.createCell(2);
				
				List<String> bchours = new LinkedList<String>();
				for (int bci = 0; bci < bclist.size(); bci++) {
					int bcHoursCol = bci * 3 + 4;
					int bcPercentCol = bci * 3 + 5;
					int bcAllocSalCol = bci * 3 + 6;
					XSSFCell bcHoursCell = r.createCell(bcHoursCol);
					bcHoursCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
					bcHoursCell.setCellStyle(csNormal);
					bcHoursCell.setCellValue(0.0);
					bchours.add(bcHoursCell.getReference());
					
					XSSFCell bcPercentCell = r.createCell(bcPercentCol);
					bcPercentCell.setCellStyle(csPercent);
					
					XSSFCell bcAllocSalCell = r.createCell(bcAllocSalCol);
					bcAllocSalCell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
					bcAllocSalCell.setCellStyle(csAllocSal);
					
				}
				
				for (int vi = 0; vi < vacationList.size(); vi++) {
					int col = bclist.size() * 3 + 4 + vi;
					XSSFCell c = r.createCell(col);
					c.setCellStyle(csNormal);
					c.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
					c.setCellValue(0.0);
					bchours.add(c.getReference());
				}
				
				User user = iterator.next();
				List<EventTable> events = eventManager.getEventByMonth(Integer.parseInt(year), Integer.parseInt(month), user.getId());
				
				XSSFCell enumcell = r.createCell(0);
				XSSFCell enamcell = r.createCell(1);
				XSSFCell mrcell = r.createCell(3);
				
				enumcell.setCellStyle(csNormal);
				enumcell.setCellValue(user.getWorkId());
				enamcell.setCellStyle(csNormal);
				enamcell.setCellValue(user.getFullName());
				mrcell.setCellStyle(csNormal);
				mrcell.setCellValue(0.0);
				
				thcell.setCellStyle(csNormal);
				
				for (EventTable eventTable : events) {
					if (eventTable.getIsReviewed() != null
							&& !eventTable.getIsPassed()) {
						// 审核不通过的事件不计入最后的考勤。
						continue;
					}
					double ehours = (eventTable.getDuration());
					
					BudgetCenter bc = eventTable.getBudgetCenter();
					int bcIndex = bclist.indexOf(bc);
					
					if (bcIndex >= 0) {
						int bcHoursCol = bcIndex * 3 + 4;
						
						XSSFCell bcHoursCell = r.getCell(bcHoursCol);
						
						double bcHours = bcHoursCell.getNumericCellValue() + ehours;
						bcHoursCell.setCellValue(bcHours);
					} else {
						bcIndex = vacationList.indexOf(bc);
						XSSFCell vacationCell = r.getCell(bcIndex + 4 + bclist.size() * 3);
						double bcHours = vacationCell.getNumericCellValue() + ehours;
						vacationCell.setCellValue(bcHours);
					}
				}
				
				thcell.setCellFormula(StringUtils.join(bchours, "+"));
			}
			
			XSSFRow totalRow = sheet.createRow(userRowIndex++);
			
			totalRow.createCell(0).setCellStyle(csNormal);
			
			XSSFCell total = totalRow.createCell(1);
			total.setCellStyle(csBold);
			total.setCellValue("Total");
			
			XSSFCell totalHours = totalRow.createCell(2);
			totalHours.setCellStyle(csBold);
			totalHours.setCellFormula("SUM(C3:C" + (3 + users.size() - 1) + ")");
			
			totalRow.createCell(3).setCellStyle(csNormal);
			
			Pattern a1pat = Pattern.compile("([A-Z]+)([0-9]+)");
			
			for (int j = 0; j < bclist.size(); j++) {
				XSSFCell bcth = totalRow.createCell(j * 3 + 4);
				bcth.setCellStyle(csBold);
				String colLabel = bcth.getReference();
				
				Matcher matcher = a1pat.matcher(colLabel);
				if (matcher.find()) {
					String cl = matcher.group(1);
					bcth.setCellFormula("SUM(" + cl + "3:" + cl + (3+users.size() - 1) + ")");
				}
				
				XSSFCell bcp = totalRow.createCell(j * 3 + 5);
				bcp.setCellStyle(csBold);
				
				XSSFCell bcalloc = totalRow.createCell(j * 3 + 6);
				bcalloc.setCellStyle(csAllocSal);
				colLabel = bcalloc.getReference();
				matcher = a1pat.matcher(colLabel);
				if (matcher.find()) {
					String cl = matcher.group(1);
					bcalloc.setCellFormula("SUM(" + cl + "3:" + cl + (3+users.size() -1) + ")");
				}
				
				int ui = 2;
				for (Iterator<User> iterator = users.iterator(); iterator.hasNext(); ui++) {
					iterator.next();
					XSSFRow ur = sheet.getRow(ui);
					XSSFCell bch = ur.getCell(j * 3 + 4);
					bcp = ur.getCell(j * 3 + 5);
					bcp.setCellFormula("+"+bch.getReference() + "/$C$" + (ui + 1));
					
					XSSFCell bcas = ur.getCell(j * 3 + 6);
					bcas.setCellFormula("+" + bcp.getReference() + "*$D$" + (ui + 1));
				}
			}
			
			for (int j = 0; j < vacationList.size(); j++) {
				XSSFCell c = totalRow.createCell(bclist.size() * 3 + 4 + j);
				c.setCellStyle(csBold);
				String colLabel = c.getReference();
				Matcher matcher = a1pat.matcher(colLabel);
				if (matcher.find()) {
					String cl = matcher.group(1);
					c.setCellFormula("SUM(" + cl + "3:" + cl + (3+users.size() - 1) + ")");
				}
			}
			
			userRowIndex += 3;
			
			XSSFRow summaryATitle = sheet.createRow(userRowIndex++);
			summaryATitle.setHeightInPoints((3*sheet.getDefaultRowHeightInPoints()));
			
			XSSFRow summaryATotal = sheet.createRow(userRowIndex + bclist.size());
			
			XSSFCell saBc = summaryATitle.createCell(0);
			saBc.setCellStyle(csBold);
			saBc.setCellValue("预算中心");
			
			XSSFCell saTH = summaryATitle.createCell(1);
			saTH.setCellStyle(csBold);
			saTH.setCellValue("Total\nHours\nA");
			
			XSSFCell saTMR = summaryATitle.createCell(2);
			saTMR.setCellStyle(csBold);
			saTMR.setCellValue("Total Monthly\nRate\nB");
			
			XSSFCell saHP = summaryATitle.createCell(3);
			saHP.setCellStyle(csBold);
			saHP.setCellValue("Hours %");
			
			XSSFCell satotalLabel = summaryATotal.createCell(0);
			satotalLabel.setCellStyle(csNormal);
			satotalLabel.setCellValue("Total");
			
			XSSFCell satotal = summaryATotal.createCell(1);			
			satotal.setCellStyle(csNormal);
			
			int s = userRowIndex;
			for (int j = 0; j < bclist.size(); j++) {
				XSSFRow bcSummaryRow = sheet.createRow(userRowIndex++);
				
				BudgetCenter bc = bclist.get(j);
				
				XSSFCell bcName = bcSummaryRow.createCell(0);
				bcName.setCellStyle(csNormal);
				bcName.setCellValue(bc.getBcName());
				
				XSSFCell bcTotal = bcSummaryRow.createCell(1);
				bcTotal.setCellStyle(csNormal);
				bcTotal.setCellFormula(sheet.getRow(3+users.size()-1).getCell(4+j*3).getReference());
				XSSFCell bcTMR = bcSummaryRow.createCell(2);
				bcTMR.setCellStyle(csNormal);
				bcTMR.setCellFormula(sheet.getRow(3+users.size()-1).getCell(6 + j * 3).getReference());
				
				XSSFCell bcTH = bcSummaryRow.createCell(3);
				bcTH.setCellStyle(csPercent);
				bcTH.setCellFormula(bcTotal.getReference()+"/" + satotal.getReference());
			}
			satotal.setCellFormula("SUM(B"+(s) + ":B" + (userRowIndex)+")");
			
			XSSFCell satm = summaryATotal.createCell(2);
			satm.setCellStyle(csNormal);
			satm.setCellFormula("SUM(C"+(s) + ":C" + (userRowIndex)+")");
			
			XSSFCell sahp = summaryATotal.createCell(3);
			sahp.setCellStyle(csPercent);
			sahp.setCellFormula("SUM(D"+(s) + ":D" + (userRowIndex)+")");
			
			wb.write(response.getOutputStream());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
		}
	}

	public void setBudgetManager(BudgetManager budgetManager) {
		this.budgetManager = budgetManager;
	}

	public void setUserManager(UserManager userManager) {
		this.userManager = userManager;
	}

	public void setEventManager(EventManager eventManager) {
		this.eventManager = eventManager;
	}
}
